Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.DirectoryServices
Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Public Class CodeMappingDAO

    Public Function GetData(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String, ByVal Text As String, ByVal Active As String) As DataTable
        Dim conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("connStr").ConnectionString)
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As New DataTable

        sql.AppendFormat(" select a.Active,c.CodeHeaderID,a.TableName,a.ColumnName,b.TableText,a.Value,a.Text,a.MOIValue,a.Sequence,ActiveText = cm_Active.Text ")
        sql.AppendFormat(",c.ColumnText ")
        sql.AppendFormat(" from codemapping a ")
        sql.AppendFormat(" inner join CodeItemView c on a.tablename = c.tablename and a.columnname = c.columnname ")
        sql.AppendFormat(" inner join codeheader b on b.codeheaderID = c.CodeHeaderID ")
        sql.AppendFormat(" inner join GetCodeMappingData('CodeMapping','Active') cm_Active on a.Active = cm_Active.Value ")
        sql.AppendFormat(" where c.AllowEdit = 'Y' ")
        sql.AppendFormat(" and a.TableName = '{0}' ", TableName)
        sql.AppendFormat(" and a.ColumnName = '{0}' ", ColumnName)

        If Value <> "" Then
            sql.AppendFormat(" and a.Value = '{0}' ", Value)
        End If

        If Text <> "" Then
            sql.AppendFormat(" and a.Text like '%{0}%' ", Text)
        End If

        'If Active <> "" Then
        '    sqlstr.AppendFormat(" and a.Active = '{0}' ", Active)
        'End If

        'Dim sda As SqlDataAdapter = New SqlDataAdapter(sqlstr.ToString(), conn)
        'Dim s As String = sqlstr.ToString()
        'conn.Open()

        'sda.Fill(dt)

        'conn.Close()
        dt = dat.GetData(sql)

        Return dt

    End Function


    Public Sub InsertData(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String, ByVal Text As String, ByVal MOIValue As String, ByVal Active As String)
        Dim dat As New clsData
        Dim sqlstr As New MyStringBuilder
        Dim dt As New DataTable

        sqlstr.AppendFormat(" insert into codemapping ")
        sqlstr.AppendFormat(" select ")
        sqlstr.AppendFormat(" '{0}', ", TableName)
        sqlstr.AppendFormat(" '{0}', ", ColumnName)
        sqlstr.AppendFormat(" '{0}', ", Value)
        sqlstr.AppendFormat(" '{0}', ", Text)
        sqlstr.AppendFormat(" '{0}', ", MOIValue)
        sqlstr.AppendFormat(" (select max(sequence)+1 from codemapping ")
        sqlstr.AppendFormat(" where TableName='{0}' ", TableName)
        sqlstr.AppendFormat(" and ColumnName='{0}'), ", ColumnName)
        sqlstr.AppendFormat(" '{0}' ", Active)

        dat.AccData(sqlstr)

    End Sub

    Public Sub UpdateData(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String, ByVal Text As String, ByVal MOIValue As String)
        Dim dat As New clsData
        Dim sqlstr As New MyStringBuilder
        Dim dt As New DataTable

        sqlstr.AppendFormat(" update codemapping ")
        sqlstr.AppendFormat(" set Text = '{0}' ", Text)
        sqlstr.AppendFormat(" ,MOIValue = '{0}' ", MOIValue)
        sqlstr.AppendFormat(" where ")
        sqlstr.AppendFormat(" TableName ='{0}' ", TableName)
        sqlstr.AppendFormat(" and ColumnName = '{0}' ", ColumnName)
        sqlstr.AppendFormat(" and Value='{0}'", Value)

        dat.AccData(sqlstr)

    End Sub


    Public Function GetdllTableTextItem() As DataTable
        Dim dat As New clsData
        Dim sqlstr As New MyStringBuilder
        Dim dt As New DataTable

        sqlstr.AppendFormat("select CodeHeaderID,TableText from codeHeader")

        dt = dat.GetData(sqlstr)

        Return dt

    End Function

    Public Function GetdllTableTextItem(ByVal codeHeaderID As String) As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As New DataTable

        sql.AppendFormat("select b.ID, b.TableName,b.ColumnName, b.IsGroup, b.ItemColumnName, b.ItemTableName, b.ItemCodeHeaderID ")
        sql.AppendFormat(",b.ColumnText ")
        sql.AppendFormat(" from codeheader a ")
        sql.AppendFormat("inner join CodeItemView b on a.CodeHeaderID = b.CodeHeaderID ")
        sql.AppendFormat(" where a.CodeHeaderID='{0}' ", codeHeaderID)

        dt = dat.GetData(sql)

        Return dt

    End Function
End Class
